﻿IF EXISTS(SELECT * FROM sys.sysdatabases WHERE name='debitDB')
	DROP DATABASE debitDB
GO

CREATE DATABASE debitDB --创建数据库
ON PRIMARY
(
	NAME='debitDB_Date',
	FILENAME='D:\SQL\MSSQL11.SQLEXPRESS\MSSQL\DATA\debitDB_Date.mdf',
	SIZE=5MB,
	MAXSIZE=100MB,
	FILEGROWTH=10%
)
LOG ON
(
	NAME='debitDB_Log',
	FILENAME='D:\SQL\MSSQL11.SQLEXPRESS\MSSQL\DATA\debitDB_Log.ldf',
	SIZE=1MB,
	FILEGROWTH=1MB
)
GO

USE debitDB
GO

CREATE TABLE [Card] --创建Card表
(
	CNo BIGINT NOT NULL PRIMARY KEY IDENTITY(10001,1),
	NAME VARCHAR(50) NOT NULL,
	ClassName VARCHAR(50) NOT NULL,
)
CREATE TABLE Books --创建Books表
(
	BNo BIGINT NOT NULL PRIMARY KEY IDENTITY(970821,1),
	BName VARCHAR(200) NOT NULL,
	Author VARCHAR(50) NOT NULL,
	Price MONEY NOT NULL,
	Quantity INT NOT NULL
)
CREATE TABLE Borrow --创建Borrow表
(
	CNo BIGINT NOT NULL,
	BNo BIGINT NOT NULL,
	RDate DATETIME NOT NULL,
	BDate DATETIME NOT NULL
	PRIMARY KEY NONCLUSTERED(CNo,BNo)
)
GO

ALTER TABLE Borrow --给Borrow表添加外键约束
	ADD CONSTRAINT FK_Borrow_Card_CNo FOREIGN KEY (CNo) REFERENCES [Card](CNo),
		CONSTRAINT FK_Borrow_Books_BNo FOREIGN KEY (BNo) REFERENCES Books(BNo)
GO

INSERT INTO [Card](Name,ClassName) VALUES('刘一','C01'), --Card表插入数据
											 ('陈二','C06'),
											 ('张三','C01'),
											 ('李四','C05'),
											 ('王五','C02'),
											 ('赵六','C04'),
											 ('孙七','C08'),
											 ('周八','C01'),
											 ('吴九','C01'),
											 ('郑十','C03');
GO

INSERT INTO Books(BName,Author,Price,Quantity) VALUES('Code Complete','Steve McConnell ',40.99,8), --Books表插入数据
													 ('The Pragmatic Programmer','Andrew Hunt',49.99,5),
													 ('Structure and Interpretation of Computer Programs - 2nd Edition (MIT)','Harold Abelson',145.56,3),
													 ('The C Programming Language','Brian W.Kernighan',67.00,7),
													 ('Introduction to Algorithms, 3rd Edition','The MIT Press',94.00,8),
													 ('Refactoring','Martin Fowler',64.99,9),
													 ('Design Patterns','rich Gamma',78.00,15),
													 ('水浒传','施耐庵',32.00,15),
													 ('网络攻防技术','吴灏',29.00,2),
													 ('Java数据结构与算法分析(第二版·影印版)','拉佛',55.10,8),
													 ('SQLServer从入门到精通（第2版）','王立平',54.5,6),
													 ('SpringBoot实战','克雷格·沃斯 ',59.5,7),
													 ('SSM轻量级框架应用实战','肖睿',56.8,8);
GO

INSERT INTO Borrow(CNo,BNo,RDate,BDate) VALUES (10001,970828,'2018-03-20 00:00:00','2018-05-03 09:15:23'),   --1)
											   (10001,970826,'2018-05-30 00:00:00','2018-05-10 12:15:42'),
											   (10002,970828,'2018-05-30 00:00:00','2018-05-12 09:15:13'),
											   (10002,970824,'2018-05-30 00:00:00','2018-05-12 09:15:13'),
											   (10002,970825,'2018-05-30 00:00:00','2018-05-12 09:15:13'),
											   (10002,970829,'2018-05-30 00:00:00','2018-05-12 09:15:13'),
											   (10002,970831,'2018-05-30 00:00:00','2018-05-12 09:15:13'),
											   (10004,970830,'2018-04-02 00:00:00','2018-05-12 08:27:15'),
											   (10005,970827,'2018-05-28 00:00:00','2018-05-03 11:12:30'),
											   (10009,970831,'2018-05-29 00:00:00','2018-03-12 08:27:15'),
											   (10010,970832,'2018-05-30 00:00:00','2018-03-13 09:12:05'),
											   (10010,970833,'2018-05-30 00:00:00','2018-03-13 09:12:05'),
											   (10008,970832,'2018-05-30 00:00:00','2018-03-13 07:02:14'),
											   (10008,970833,'2018-05-30 00:00:00','2018-03-13 07:02:14');
GO

SELECT CNo AS '借书卡号',COUNT(BNo) AS '借图书册数' FROM Borrow GROUP BY CNo HAVING COUNT(BNo)>=5; --2)

SELECT Name AS '姓名',ClassName AS '班级名称' FROM [Card] WHERE CNo IN (SELECT CNo FROM Borrow WHERE BNo=(SELECT BNo FROM Books  WHERE BName='水浒传')); --3)

SELECT CNo AS '借书卡号',BNo AS '书号',RDate AS '还书日期' FROM Borrow WHERE RDate<GETDATE(); --4)

SELECT BNo AS '书号',BName AS '书名',Author AS '作者' FROM Books WHERE BName LIKE '%网络%'; --5)

SELECT BName AS '书名',Author AS '作者' FROM Books WHERE Price=(SELECT MAX(Price) FROM Books); --6)

SELECT CNo AS '借书卡号' FROM Borrow 
WHERE BNo=(SELECT BNo FROM Books WHERE BName='Java数据结构与算法分析(第二版·影印版)') AND BNo <> (SELECT BNo FROM Books WHERE BName='SQLServer从入门到精通（第2版）');--7)

UPDATE Borrow SET RDate=(SELECT  DATEADD(DAY,7,GETDATE())) WHERE CNo IN (SELECT CNo FROM [Card] WHERE ClassName='C01'); --8)

DELETE FROM Books WHERE BNo IN (SELECT BNo FROM Books WHERE BNo NOT IN (SELECT BNo FROM Borrow)); --9)


SELECT CNo AS '借记卡号' FROM Borrow WHERE CNo IN (SELECT CNo FROM Borrow 
WHERE BNo=(SELECT BNo FROM Books WHERE BName='SpringBoot实战')) AND BNo=(SELECT BNo FROM Books WHERE BName='SSM轻量级框架应用实战') ORDER BY CNo; --10)